package org.erp.spu.service.impl;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import lombok.extern.log4j.Log4j2;
import org.erp.spu.entity.*;
import org.erp.spu.entity.vo.*;
import org.erp.spu.mapper.*;
import org.erp.spu.service.ExcelImportExportService;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.IOException;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.ArrayList;

@Log4j2
@Service
public class ExcelImportExportServiceImpl implements ExcelImportExportService {
    @Resource
    private SpuModelAttributeMapper spuModelAttributeMapper;

    @Resource
    private SpuReleaseInfoMapper spuReleaseInfoMapper;

    @Resource
    private SpuModelTemplateMapper spuModelTemplateMapper;

    @Resource
    private SpuTemplateInfoMapper spuTemplateInfoMapper;

    @Resource
    private SpuTemplateReleaseMapper spuTemplateReleaseMapper;

    @Resource
    private SpuCategoryMapper spuCategoryMapper;

    @Resource
    private SpuBrandMapper spuBrandMapper;

    @Resource
    private SpuSeriesMapper spuSeriesMapper;

    @Resource
    private SpuModelMapper spuModelMapper;

    @Resource
    private SpuAttributeMapper spuAttributeMapper;

    @Resource
    private SpuCategoryBrandMapper spuCategoryBrandMapper;

    @Resource
    private SpuReleaseGroupMapper spuReleaseGroupMapper;

    @Resource
    private SpuTemplateVersionMapper spuTemplateVersionMapper;

    @Override
    public List<SpuAttrExcelVo> getSpuAllExportListByCategoryIdByPage(Long categoryId, int page, int pageSize) {
        long offset = (long) Math.max(page - 1, 0) * Math.max(pageSize, 1);
        log.info("分页获取导出数据 categoryId={}, page={}, pageSize={}, offset={}", categoryId, page, pageSize, offset);
        return spuModelAttributeMapper.getSpuAllExportListByCategoryIdByPage(categoryId, offset, pageSize);
    }

    @Override
    public List<SpuAllReleaseExcelVo> getSpuAllReleaseExportListByCategoryId(Long categoryId) {
        List<SpuReleaseInfo> list = spuReleaseInfoMapper.selectList(new LambdaQueryWrapper<SpuReleaseInfo>()
                .eq(categoryId != null, SpuReleaseInfo::getCategoryId, categoryId).eq(SpuReleaseInfo::getType, 0)
                .eq(SpuReleaseInfo::getDelFlag, 0).orderByAsc(SpuReleaseInfo::getParentId)
                .orderByAsc(SpuReleaseInfo::getId));
        List<SpuAllReleaseExcelVo> excelVoList = list.stream().map(releaseInfo -> {
            SpuAllReleaseExcelVo excelVo = new SpuAllReleaseExcelVo();
            BeanUtils.copyProperties(releaseInfo, excelVo);
            return excelVo;
        }).toList();
        return excelVoList;
    }

    @Override
    public List<SpuReleaseInfo> getSpuReleaseListByCategoryId(Long categoryId) {
        return spuReleaseInfoMapper.selectList(new LambdaQueryWrapper<SpuReleaseInfo>()
                .eq(categoryId != null, SpuReleaseInfo::getCategoryId, categoryId).eq(SpuReleaseInfo::getType, 1)
                .eq(SpuReleaseInfo::getDelFlag, 0).orderByAsc(SpuReleaseInfo::getReleaseGroupId)
                .orderByAsc(SpuReleaseInfo::getId));
    }

    @Override
    public List<SpuModelTemplateExcelVo> getModelTemplateExportByCategoryId(Long categoryId, int page, int pageSize) {
        long offset = (long) Math.max(page - 1, 0) * Math.max(pageSize, 1);
        log.info("分页获取导出数据 categoryId={}, page={}, pageSize={}, offset={}", categoryId, page, pageSize, offset);
        return spuModelTemplateMapper.getModelTemplateExportByCategoryId(categoryId, offset, pageSize);
    }

    @Override
    public List<SpuTemplateInfoVo> getTemplateInfoList(Long categoryId) {
        return spuTemplateInfoMapper.getTemplateInfoList(categoryId);
    }

    @Override
    public List<SpuTemplateReleaseExcelVo> getReleaseListByTemplateId(Long templateId) {
        return spuTemplateReleaseMapper.getReleaseListByTemplateId(templateId);
    }

    @Override
    public String importAttrExcel(MultipartFile file) {
        try {
            ImportParams params = new ImportParams();
            params.setHeadRows(1);
            params.setTitleRows(0);

            List<SpuAttrExcelVo> rows = ExcelImportUtil.importExcel(file.getInputStream(), SpuAttrExcelVo.class, params);
            if (rows == null || rows.isEmpty()) {
                return "导入完成：0条记录";
            }
            // 1) 预扫描构建唯一键集合（支持ID优先），减少后续查询与插入次数
            Set<String> categoryNames = new HashSet<>();
            Set<String> brandNames = new HashSet<>();
            Set<String> cbNameKeys = new HashSet<>(); // categoryName::brandName
            Set<String> seriesNameKeys = new HashSet<>(); // seriesName::brandName::categoryName
            Set<String> parentNames = new HashSet<>();
            Set<String> childNameKeys = new HashSet<>(); // parentName::attrName
            Set<String> modelNameSet = new HashSet<>();
            // 额外：收集ID集合（若Excel提供则优先使用）
            Set<Long> categoryIdSet = new HashSet<>();
            Set<Long> brandIdSet = new HashSet<>();
            Set<Long> seriesIdSet = new HashSet<>();
            Set<Long> modelIdSet = new HashSet<>();
            Set<Long> parentAttrIdSet = new HashSet<>();
            Set<Long> childAttrIdSet = new HashSet<>();
            List<SpuAttrExcelVo> validRows = new ArrayList<>();

            for (SpuAttrExcelVo vo : rows) {
                if (vo == null) continue;
                String categoryName = safeTrim(vo.getCategoryName());
                String brandName = safeTrim(vo.getBrandName());
                String seriesName = safeTrim(vo.getSeriesName());
                String modelName = safeTrim(vo.getModelName());
                String parentName = safeTrim(vo.getAttributeParentName());
                String attrName = safeTrim(vo.getAttributeName());
                // 行有效性：关键字段需至少提供ID或名称（ID优先）
                boolean categoryOk = (vo.getCategoryId() != null) || !isBlank(categoryName);
                boolean brandOk = (vo.getBrandId() != null) || !isBlank(brandName);
                boolean modelOk = (vo.getId() != null) || !isBlank(modelName);
                boolean parentOk = (vo.getAttributeParentId() != null) || !isBlank(parentName);
                boolean attrOk = (vo.getAttributeId() != null) || !isBlank(attrName);
                if (!(categoryOk && brandOk && modelOk && parentOk && attrOk)) {
                    continue;
                }
                // 收集唯一集合
                if (!isBlank(categoryName)) categoryNames.add(categoryName);
                if (!isBlank(brandName)) brandNames.add(brandName);
                if (!isBlank(categoryName) && !isBlank(brandName)) {
                    cbNameKeys.add(categoryName + "::" + brandName);
                }
                if (!isBlank(seriesName) && !isBlank(brandName) && !isBlank(categoryName)) {
                    seriesNameKeys.add(seriesName + "::" + brandName + "::" + categoryName);
                }
                if (!isBlank(modelName)) modelNameSet.add(modelName);
                if (!isBlank(parentName)) parentNames.add(parentName);
                if (!isBlank(parentName) && !isBlank(attrName)) childNameKeys.add(parentName + "::" + attrName);

                if (vo.getCategoryId() != null) categoryIdSet.add(vo.getCategoryId());
                if (vo.getBrandId() != null) brandIdSet.add(vo.getBrandId());
                if (vo.getSeriesId() != null) seriesIdSet.add(vo.getSeriesId());
                if (vo.getId() != null) modelIdSet.add(vo.getId());
                if (vo.getAttributeParentId() != null) parentAttrIdSet.add(vo.getAttributeParentId());
                if (vo.getAttributeId() != null) childAttrIdSet.add(vo.getAttributeId());
                validRows.add(vo);
            }

            // 名称->ID 映射缓存
            Map<String, Long> categoryMap = new HashMap<>();
            Map<String, Long> brandMap = new HashMap<>();
            Map<String, Long> cbMap = new HashMap<>(); // key: categoryId::brandId
            Map<String, Long> seriesMap = new HashMap<>(); // key: seriesName::brandId::categoryId
            Map<String, Long> modelMap = new HashMap<>(); // key: modelName::seriesId
            Map<String, Long> parentAttrMap = new HashMap<>(); // parentName
            Map<String, Long> childAttrMap = new HashMap<>(); // key: parentId::attrName
            // ID存在集合（用于快速判断ID是否有效）
            Set<Long> categoryIdExist = new HashSet<>();
            Set<Long> brandIdExist = new HashSet<>();
            Set<Long> seriesIdExist = new HashSet<>();
            Set<Long> modelIdExist = new HashSet<>();
            Set<Long> parentAttrIdExist = new HashSet<>();
            Set<Long> childAttrIdExist = new HashSet<>();

            int addCategory = 0, addBrand = 0, addCategoryBrand = 0, addSeries = 0, addModel = 0, addAttrParent = 0, addAttrChild = 0, addModelAttr = 0;

            // 2) 预加载已存在的类目、品牌（支持ID与名称）
            if (!categoryIdSet.isEmpty()) {
                List<SpuCategory> existCatsById = spuCategoryMapper.selectList(new LambdaQueryWrapper<SpuCategory>()
                        .in(true, SpuCategory::getId, categoryIdSet)
                        .eq(SpuCategory::getDelFlag, 0));
                for (SpuCategory c : existCatsById) {
                    categoryIdExist.add(c.getId());
                    categoryMap.put(c.getCategoryName(), c.getId());
                }
            }
            if (!categoryNames.isEmpty()) {
                List<SpuCategory> existCats = spuCategoryMapper.selectList(new LambdaQueryWrapper<SpuCategory>()
                        .in(true, SpuCategory::getCategoryName, categoryNames)
                        .eq(SpuCategory::getDelFlag, 0));
                for (SpuCategory c : existCats) {
                    categoryMap.put(c.getCategoryName(), c.getId());
                    categoryIdExist.add(c.getId());
                }
                for (String name : categoryNames) {
                    if (!categoryMap.containsKey(name)) {
                        // 若有同名行提供了Excel分类ID但该ID尚未存在，优先走ID插入，跳过名称插入避免重复
                        boolean preferId = false;
                        for (SpuAttrExcelVo v : validRows) {
                            if (!isBlank(safeTrim(v.getCategoryName())) && name.equals(safeTrim(v.getCategoryName()))
                                    && v.getCategoryId() != null && !categoryIdExist.contains(v.getCategoryId())) {
                                preferId = true;
                                break;
                            }
                        }
                        if (preferId) continue;
                        SpuCategory cat = new SpuCategory();
                        cat.setCategoryName(name);
                        cat.setStatus("1");
                        cat.setSort(0);
                        cat.setParentId(0L);
                        cat.setCategoryLevel(1);
                        if (spuCategoryMapper.insert(cat) > 0) {
                            categoryMap.put(name, cat.getId());
                            categoryIdExist.add(cat.getId());
                            addCategory++;
                        }
                    }
                }
            }

            // 2a) 按Excel提供的分类ID补充创建（ID优先）：若ID不存在则以该ID插入
            for (SpuAttrExcelVo vo : validRows) {
                Long cId = vo.getCategoryId();
                if (cId != null && !categoryIdExist.contains(cId)) {
                    String cName = safeTrim(vo.getCategoryName());
                    SpuCategory cat = new SpuCategory();
                    cat.setId(cId);
                    cat.setCategoryName(cName);
                    cat.setStatus("1");
                    cat.setSort(0);
                    cat.setParentId(0L);
                    cat.setCategoryLevel(1);
                    if (spuCategoryMapper.insert(cat) > 0) {
                        if (!isBlank(cName)) {
                            categoryMap.put(cName, cId);
                        }
                        categoryIdExist.add(cId);
                        addCategory++;
                    }
                }
            }

            if (!brandIdSet.isEmpty()) {
                List<SpuBrand> existBrandsById = spuBrandMapper.selectList(new LambdaQueryWrapper<SpuBrand>()
                        .in(true, SpuBrand::getId, brandIdSet)
                        .eq(SpuBrand::getDelFlag, 0));
                for (SpuBrand b : existBrandsById) {
                    brandIdExist.add(b.getId());
                    brandMap.put(b.getBrandName(), b.getId());
                }
            }
            if (!brandNames.isEmpty()) {
                List<SpuBrand> existBrands = spuBrandMapper.selectList(new LambdaQueryWrapper<SpuBrand>()
                        .in(true, SpuBrand::getBrandName, brandNames)
                        .eq(SpuBrand::getDelFlag, 0));
                for (SpuBrand b : existBrands) {
                    brandMap.put(b.getBrandName(), b.getId());
                    brandIdExist.add(b.getId());
                }
                for (String name : brandNames) {
                    if (!brandMap.containsKey(name)) {
                        // 若有同名行提供了Excel品牌ID但该ID尚未存在，优先走ID插入，跳过名称插入避免重复
                        boolean preferId = false;
                        for (SpuAttrExcelVo v : validRows) {
                            if (!isBlank(safeTrim(v.getBrandName())) && name.equals(safeTrim(v.getBrandName()))
                                    && v.getBrandId() != null && !brandIdExist.contains(v.getBrandId())) {
                                preferId = true;
                                break;
                            }
                        }
                        if (preferId) continue;
                        SpuBrand brand = new SpuBrand();
                        brand.setBrandName(name);
                        brand.setStatus("1");
                        brand.setSort(0);
                        if (spuBrandMapper.insert(brand) > 0) {
                            brandMap.put(name, brand.getId());
                            brandIdExist.add(brand.getId());
                            addBrand++;
                        }
                    }
                }
            }

            // 2b) 按Excel提供的品牌ID补充创建（ID优先）：若ID不存在则以该ID插入
            for (SpuAttrExcelVo vo : validRows) {
                Long bId = vo.getBrandId();
                if (bId != null && !brandIdExist.contains(bId)) {
                    String bName = safeTrim(vo.getBrandName());
                    SpuBrand brand = new SpuBrand();
                    brand.setId(bId);
                    brand.setBrandName(bName);
                    brand.setStatus("1");
                    brand.setSort(0);
                    if (spuBrandMapper.insert(brand) > 0) {
                        if (!isBlank(bName)) {
                            brandMap.put(bName, bId);
                        }
                        brandIdExist.add(bId);
                        addBrand++;
                    }
                }
            }

            // 3) 分类品牌关系一次性处理
            Set<Long> catIds = new HashSet<>();
            for (Long id : categoryMap.values()) catIds.add(id);
            Set<Long> brandIds = new HashSet<>();
            for (Long id : brandMap.values()) brandIds.add(id);
            // 合并Excel提供的ID集合
            catIds.addAll(categoryIdSet);
            brandIds.addAll(brandIdSet);
            if (!catIds.isEmpty() && !brandIds.isEmpty()) {
                List<SpuCategoryBrand> existCbList = spuCategoryBrandMapper.selectList(new LambdaQueryWrapper<SpuCategoryBrand>()
                        .in(true, SpuCategoryBrand::getCategoryId, catIds)
                        .in(true, SpuCategoryBrand::getBrandId, brandIds)
                        .eq(SpuCategoryBrand::getDelFlag, 0));
                for (SpuCategoryBrand cb : existCbList) {
                    cbMap.put(cb.getCategoryId() + "::" + cb.getBrandId(), cb.getId());
                }
                for (String cbNameKey : cbNameKeys) {
                    String[] parts = cbNameKey.split("::");
                    String cName = parts[0];
                    String bName = parts[1];
                    Long cId = categoryMap.get(cName);
                    Long bId = brandMap.get(bName);
                    if (cId == null || bId == null) continue;
                    String idKey = cId + "::" + bId;
                    if (!cbMap.containsKey(idKey)) {
                        SpuCategoryBrand cb = new SpuCategoryBrand();
                        cb.setCategoryId(cId);
                        cb.setCategoryName(cName);
                        cb.setBrandId(bId);
                        cb.setBrandName(bName);
                        if (spuCategoryBrandMapper.insert(cb) > 0) {
                            cbMap.put(idKey, cb.getId());
                            addCategoryBrand++;
                        }
                    }
                }
            }

            // 4) 系列一次性处理
            Set<String> seriesNamesOnly = new HashSet<>();
            for (String key : seriesNameKeys) {
                seriesNamesOnly.add(key.split("::")[0]);
            }
            if (!seriesIdSet.isEmpty()) {
                List<SpuSeries> existSeriesById = spuSeriesMapper.selectList(new LambdaQueryWrapper<SpuSeries>()
                        .in(true, SpuSeries::getId, seriesIdSet)
                        .eq(SpuSeries::getDelFlag, 0));
                for (SpuSeries s : existSeriesById) {
                    seriesIdExist.add(s.getId());
                    seriesMap.put(s.getSeriesName() + "::" + s.getBrandId() + "::" + s.getCategoryId(), s.getId());
                }
            }
            if (!seriesNameKeys.isEmpty()) {
                List<SpuSeries> existSeries = spuSeriesMapper.selectList(new LambdaQueryWrapper<SpuSeries>()
                        .in(true, SpuSeries::getSeriesName, seriesNamesOnly)
                        .in(!brandIds.isEmpty(), SpuSeries::getBrandId, brandIds)
                        .in(!catIds.isEmpty(), SpuSeries::getCategoryId, catIds)
                        .eq(SpuSeries::getDelFlag, 0));
                for (SpuSeries s : existSeries) {
                    seriesMap.put(s.getSeriesName() + "::" + s.getBrandId() + "::" + s.getCategoryId(), s.getId());
                }
                for (String sk : seriesNameKeys) {
                    String[] parts = sk.split("::");
                    String sName = parts[0];
                    String bName = parts[1];
                    String cName = parts[2];
                    Long bId = brandMap.get(bName);
                    Long cId = categoryMap.get(cName);
                    if (bId == null || cId == null) continue;
                    String sKey = sName + "::" + bId + "::" + cId;
                    if (!seriesMap.containsKey(sKey)) {
                        // 若有同名行提供了Excel系列ID但该ID尚未存在，优先走ID插入，跳过名称插入避免重复
                        boolean preferId = false;
                        for (SpuAttrExcelVo v : validRows) {
                            if (!isBlank(safeTrim(v.getSeriesName())) && sName.equals(safeTrim(v.getSeriesName()))
                                    && v.getSeriesId() != null && !seriesIdExist.contains(v.getSeriesId())) {
                                preferId = true;
                                break;
                            }
                        }
                        if (preferId) continue;
                        // 找到分类品牌关系ID
                        String cbIdKey = cId + "::" + bId;
                        Long cbId = cbMap.get(cbIdKey);
                        SpuSeries series = new SpuSeries();
                        series.setSeriesName(sName);
                        series.setBrandId(bId);
                        series.setBrandName(bName);
                        series.setCategoryId(cId);
                        series.setCategoryName(cName);
                        series.setCategoryBrandId(cbId);
                        series.setSort(0);
                        if (spuSeriesMapper.insert(series) > 0) {
                            seriesMap.put(sKey, series.getId());
                            addSeries++;
                        }
                    }
                }
            }

            // 4a) 按Excel提供的系列ID补充创建（ID优先）：若ID不存在则以该ID插入
            for (SpuAttrExcelVo vo : validRows) {
                Long sIdExcel = vo.getSeriesId();
                if (sIdExcel != null && !seriesIdExist.contains(sIdExcel)) {
                    String cName = safeTrim(vo.getCategoryName());
                    String bName = safeTrim(vo.getBrandName());
                    String sName = safeTrim(vo.getSeriesName());
                    Long cId = (vo.getCategoryId() != null && categoryIdExist.contains(vo.getCategoryId()))
                            ? vo.getCategoryId() : categoryMap.get(cName);
                    Long bId = (vo.getBrandId() != null && brandIdExist.contains(vo.getBrandId()))
                            ? vo.getBrandId() : brandMap.get(bName);
                    if (cId == null || bId == null) continue;
                    String cbKey = cId + "::" + bId;
                    Long cbId = cbMap.get(cbKey);
                    SpuSeries series = new SpuSeries();
                    series.setId(sIdExcel);
                    series.setSeriesName(sName);
                    series.setBrandId(bId);
                    series.setBrandName(bName);
                    series.setCategoryId(cId);
                    series.setCategoryName(cName);
                    series.setCategoryBrandId(cbId);
                    series.setSort(0);
                    if (spuSeriesMapper.insert(series) > 0) {
                        if (!isBlank(sName)) {
                            seriesMap.put(sName + "::" + bId + "::" + cId, sIdExcel);
                        }
                        seriesIdExist.add(sIdExcel);
                        addSeries++;
                    }
                }
            }

            // 5) 型号一次性处理（基于型号名称全局唯一），无需系列过滤
            if (!modelIdSet.isEmpty()) {
                List<SpuModel> existModelsById = spuModelMapper.selectList(new LambdaQueryWrapper<SpuModel>()
                        .in(true, SpuModel::getId, modelIdSet)
                        .eq(SpuModel::getDelFlag, 0));
                for (SpuModel m : existModelsById) {
                    modelIdExist.add(m.getId());
                    modelMap.put(m.getModelName(), m.getId());
                }
            }
            if (!modelNameSet.isEmpty()) {
                List<SpuModel> existModels = spuModelMapper.selectList(new LambdaQueryWrapper<SpuModel>()
                        .in(true, SpuModel::getModelName, modelNameSet)
                        .eq(SpuModel::getDelFlag, 0));
                for (SpuModel m : existModels) {
                    modelMap.put(m.getModelName(), m.getId());
                }
            }

            // 按行建立缺失型号并映射（若系列为空则允许 seriesId=null）
            for (SpuAttrExcelVo vo : validRows) {
                String cName = safeTrim(vo.getCategoryName());
                String bName = safeTrim(vo.getBrandName());
                String sName = safeTrim(vo.getSeriesName());
                String mName = safeTrim(vo.getModelName());
                Long cId = (vo.getCategoryId() != null && categoryIdExist.contains(vo.getCategoryId()))
                        ? vo.getCategoryId() : categoryMap.get(cName);
                Long bId = (vo.getBrandId() != null && brandIdExist.contains(vo.getBrandId()))
                        ? vo.getBrandId() : brandMap.get(bName);
                if (cId == null || bId == null) continue;
                Long sId = null;
                if (vo.getSeriesId() != null && seriesIdExist.contains(vo.getSeriesId())) {
                    sId = vo.getSeriesId();
                } else if (!isBlank(sName)) {
                    String sKey = sName + "::" + bId + "::" + cId;
                    sId = seriesMap.get(sKey);
                }
                // 若Excel提供型号ID且存在，则无需创建
                if (vo.getId() != null && modelIdExist.contains(vo.getId())) {
                    // no-op
                } else {
                    // 若Excel提供型号ID但不存在，则以该ID插入；否则按名称插入
                    if (vo.getId() != null && !modelIdExist.contains(vo.getId())) {
                        SpuModel model = new SpuModel();
                        model.setId(vo.getId());
                        model.setModelName(mName);
                        model.setSeriesId(sId);
                        model.setSeriesName(isBlank(sName) ? null : sName);
                        model.setBrandId(bId);
                        model.setBrandName(bName);
                        model.setCategoryId(cId);
                        model.setCategoryName(cName);
                        if (spuModelMapper.insert(model) > 0) {
                            if (!isBlank(mName)) {
                                modelMap.put(mName, vo.getId());
                            }
                            modelIdExist.add(vo.getId());
                            addModel++;
                        }
                    } else {
                        String mKey = mName;
                        if (!isBlank(mKey) && !modelMap.containsKey(mKey)) {
                            // 查询一次避免重复插入（按型号名称全局唯一）
                            SpuModel existModel = spuModelMapper.selectOne(new LambdaQueryWrapper<SpuModel>()
                                    .eq(SpuModel::getModelName, mName)
                                    .eq(SpuModel::getDelFlag, 0)
                                    .last("limit 1"));
                            if (existModel != null) {
                                modelMap.put(mKey, existModel.getId());
                            } else {
                                SpuModel model = new SpuModel();
                                model.setModelName(mName);
                                model.setSeriesId(sId);
                                model.setSeriesName(isBlank(sName) ? null : sName);
                                model.setBrandId(bId);
                                model.setBrandName(bName);
                                model.setCategoryId(cId);
                                model.setCategoryName(cName);
                                if (spuModelMapper.insert(model) > 0) {
                                    modelMap.put(mKey, model.getId());
                                    addModel++;
                                }
                            }
                        }
                    }
                }
            }

            // 6) 属性父级与子级一次性处理
            if (!parentAttrIdSet.isEmpty()) {
                List<SpuAttribute> existParentsById = spuAttributeMapper.selectList(new LambdaQueryWrapper<SpuAttribute>()
                        .in(true, SpuAttribute::getId, parentAttrIdSet)
                        .eq(SpuAttribute::getType, 1)
                        .eq(SpuAttribute::getDelFlag, 0));
                for (SpuAttribute p : existParentsById) {
                    parentAttrIdExist.add(p.getId());
                    parentAttrMap.put(p.getAttributeName(), p.getId());
                }
            }
            if (!parentNames.isEmpty()) {
                List<SpuAttribute> existParents = spuAttributeMapper.selectList(new LambdaQueryWrapper<SpuAttribute>()
                        .in(true, SpuAttribute::getAttributeName, parentNames)
                        .eq(SpuAttribute::getType, 1)
                        .eq(SpuAttribute::getDelFlag, 0));
                for (SpuAttribute p : existParents) {
                    parentAttrMap.put(p.getAttributeName(), p.getId());
                    parentAttrIdExist.add(p.getId());
                }
                for (String pName : parentNames) {
                    if (!parentAttrMap.containsKey(pName)) {
                        // 若有同名行提供了Excel父属性ID但该ID尚未存在，优先走ID插入，跳过名称插入避免重复
                        boolean preferId = false;
                        for (SpuAttrExcelVo v : validRows) {
                            if (!isBlank(safeTrim(v.getAttributeParentName())) && pName.equals(safeTrim(v.getAttributeParentName()))
                                    && v.getAttributeParentId() != null && !parentAttrIdExist.contains(v.getAttributeParentId())) {
                                preferId = true;
                                break;
                            }
                        }
                        if (preferId) continue;
                        SpuAttribute parentAttr = new SpuAttribute();
                        parentAttr.setAttributeName(pName);
                        parentAttr.setParentId(0L);
                        parentAttr.setParentAttributeName(null);
                        parentAttr.setType(1);
                        parentAttr.setStatus("1");
                        if (spuAttributeMapper.insert(parentAttr) > 0) {
                            parentAttrMap.put(pName, parentAttr.getId());
                            parentAttrIdExist.add(parentAttr.getId());
                            addAttrParent++;
                        }
                    }
                }
            }

            // 6a) 按Excel提供的父属性ID补充创建（ID优先）：若ID不存在则以该ID插入
            for (SpuAttrExcelVo vo : validRows) {
                Long pIdExcel = vo.getAttributeParentId();
                if (pIdExcel != null && !parentAttrIdExist.contains(pIdExcel)) {
                    String pName = safeTrim(vo.getAttributeParentName());
                    SpuAttribute parentAttr = new SpuAttribute();
                    parentAttr.setId(pIdExcel);
                    parentAttr.setAttributeName(pName);
                    parentAttr.setParentId(0L);
                    parentAttr.setParentAttributeName(null);
                    parentAttr.setType(1);
                    parentAttr.setStatus("1");
                    if (spuAttributeMapper.insert(parentAttr) > 0) {
                        if (!isBlank(pName)) {
                            parentAttrMap.put(pName, pIdExcel);
                        }
                        parentAttrIdExist.add(pIdExcel);
                        addAttrParent++;
                    }
                }
            }

            // 子级属性
            Set<String> childNamesOnly = new HashSet<>();
            for (String ck : childNameKeys) childNamesOnly.add(ck.split("::")[1]);
            Set<Long> parentIds = new HashSet<>();
            for (Long id : parentAttrMap.values()) parentIds.add(id);
            parentIds.addAll(parentAttrIdSet);
            if (!childAttrIdSet.isEmpty()) {
                List<SpuAttribute> existChildrenById = spuAttributeMapper.selectList(new LambdaQueryWrapper<SpuAttribute>()
                        .in(true, SpuAttribute::getId, childAttrIdSet)
                        .eq(SpuAttribute::getType, 0)
                        .eq(SpuAttribute::getDelFlag, 0));
                for (SpuAttribute ch : existChildrenById) {
                    childAttrIdExist.add(ch.getId());
                    childAttrMap.put(ch.getParentId() + "::" + ch.getAttributeName(), ch.getId());
                }
            }
            if (!childNameKeys.isEmpty() && !parentIds.isEmpty()) {
                List<SpuAttribute> existChildren = spuAttributeMapper.selectList(new LambdaQueryWrapper<SpuAttribute>()
                        .in(true, SpuAttribute::getParentId, parentIds)
                        .in(true, SpuAttribute::getAttributeName, childNamesOnly)
                        .eq(SpuAttribute::getType, 0)
                        .eq(SpuAttribute::getDelFlag, 0));
                for (SpuAttribute ch : existChildren) {
                    childAttrMap.put(ch.getParentId() + "::" + ch.getAttributeName(), ch.getId());
                    childAttrIdExist.add(ch.getId());
                }
                for (String ck : childNameKeys) {
                    String[] parts = ck.split("::");
                    String pName = parts[0];
                    String aName = parts[1];
                    Long pId = parentAttrMap.get(pName);
                    if (pId == null) continue;
                    String idKey = pId + "::" + aName;
                    if (!childAttrMap.containsKey(idKey)) {
                        // 若有同名行提供了Excel子属性ID但该ID尚未存在，优先走ID插入，跳过名称插入避免重复
                        boolean preferId = false;
                        for (SpuAttrExcelVo v : validRows) {
                            boolean sameParent = !isBlank(safeTrim(v.getAttributeParentName())) && pName.equals(safeTrim(v.getAttributeParentName()));
                            boolean sameAttr = !isBlank(safeTrim(v.getAttributeName())) && aName.equals(safeTrim(v.getAttributeName()));
                            if (sameParent && sameAttr && v.getAttributeId() != null && !childAttrIdExist.contains(v.getAttributeId())) {
                                preferId = true;
                                break;
                            }
                        }
                        if (preferId) continue;
                        SpuAttribute attr = new SpuAttribute();
                        attr.setAttributeName(aName);
                        attr.setParentId(pId);
                        attr.setParentAttributeName(pName);
                        attr.setType(0);
                        attr.setStatus("1");
                        if (spuAttributeMapper.insert(attr) > 0) {
                            childAttrMap.put(idKey, attr.getId());
                            childAttrIdExist.add(attr.getId());
                            addAttrChild++;
                        }
                    }
                }
            }

            // 6b) 按Excel提供的子属性ID补充创建（ID优先）：若ID不存在则以该ID插入
            for (SpuAttrExcelVo vo : validRows) {
                Long pId = (vo.getAttributeParentId() != null && parentAttrIdExist.contains(vo.getAttributeParentId()))
                        ? vo.getAttributeParentId() : parentAttrMap.get(safeTrim(vo.getAttributeParentName()));
                Long aIdExcel = vo.getAttributeId();
                String aName = safeTrim(vo.getAttributeName());
                String pName = safeTrim(vo.getAttributeParentName());
                if (aIdExcel != null && pId != null && !childAttrIdExist.contains(aIdExcel)) {
                    SpuAttribute attr = new SpuAttribute();
                    attr.setId(aIdExcel);
                    attr.setAttributeName(aName);
                    attr.setParentId(pId);
                    attr.setParentAttributeName(pName);
                    attr.setType(0);
                    attr.setStatus("1");
                    if (spuAttributeMapper.insert(attr) > 0) {
                        childAttrMap.put(pId + "::" + aName, aIdExcel);
                        childAttrIdExist.add(aIdExcel);
                        addAttrChild++;
                    }
                }
            }

            // 7) 型号-属性关系：先查询所有涉及型号的现有关系，再按行批量插入缺失的
            Set<Long> modelIds = new HashSet<>();
            for (SpuAttrExcelVo vo : validRows) {
                String cName = safeTrim(vo.getCategoryName());
                String bName = safeTrim(vo.getBrandName());
                String sName = safeTrim(vo.getSeriesName());
                String mName = safeTrim(vo.getModelName());
                Long cId = (vo.getCategoryId() != null && categoryIdExist.contains(vo.getCategoryId()))
                        ? vo.getCategoryId() : categoryMap.get(cName);
                Long bId = (vo.getBrandId() != null && brandIdExist.contains(vo.getBrandId()))
                        ? vo.getBrandId() : brandMap.get(bName);
                if (cId == null || bId == null) continue;
                Long mId = null;
                if (vo.getId() != null && modelIdExist.contains(vo.getId())) {
                    mId = vo.getId();
                } else {
                    mId = modelMap.get(mName);
                }
                if (mId != null) modelIds.add(mId);
            }

            Set<String> existRelKeys = new HashSet<>();
            if (!modelIds.isEmpty()) {
                List<SpuModelAttribute> existRels = spuModelAttributeMapper.selectList(new LambdaQueryWrapper<SpuModelAttribute>()
                        .in(true, SpuModelAttribute::getModelId, modelIds)
                        .eq(SpuModelAttribute::getDelFlag, 0));
                for (SpuModelAttribute r : existRels) {
                    existRelKeys.add(r.getModelId() + "::" + r.getAttributeParentId() + "::" + r.getAttributeId());
                }
            }

            Set<String> newRelKeys = new HashSet<>();
            for (SpuAttrExcelVo vo : validRows) {
                String cName = safeTrim(vo.getCategoryName());
                String bName = safeTrim(vo.getBrandName());
                String sName = safeTrim(vo.getSeriesName());
                String mName = safeTrim(vo.getModelName());
                String pName = safeTrim(vo.getAttributeParentName());
                String aName = safeTrim(vo.getAttributeName());
                Long cId = (vo.getCategoryId() != null && categoryIdExist.contains(vo.getCategoryId()))
                        ? vo.getCategoryId() : categoryMap.get(cName);
                Long bId = (vo.getBrandId() != null && brandIdExist.contains(vo.getBrandId()))
                        ? vo.getBrandId() : brandMap.get(bName);
                if (cId == null || bId == null) continue;
                Long mId = (vo.getId() != null && modelIdExist.contains(vo.getId()))
                        ? vo.getId() : modelMap.get(mName);
                Long pId = (vo.getAttributeParentId() != null && parentAttrIdExist.contains(vo.getAttributeParentId()))
                        ? vo.getAttributeParentId() : parentAttrMap.get(pName);
                Long aId = (vo.getAttributeId() != null && childAttrIdExist.contains(vo.getAttributeId()))
                        ? vo.getAttributeId() : (pId == null ? null : childAttrMap.get(pId + "::" + aName));
                if (mId == null || pId == null || aId == null) continue;
                String relKey = mId + "::" + pId + "::" + aId;
                if (!existRelKeys.contains(relKey)) {
                    newRelKeys.add(relKey);
                    SpuModelAttribute rel = new SpuModelAttribute();
                    rel.setModelId(mId);
                    rel.setModelName(mName);
                    rel.setAttributeParentId(pId);
                    rel.setAttributeParentName(pName);
                    rel.setAttributeId(aId);
                    rel.setAttributeName(aName);
                    if (spuModelAttributeMapper.insert(rel) > 0) {
                        addModelAttr++;
                    }
                }
            }

            String summary = String.format(
                    "导入完成（ID优先优化版）：新增 类目%d、品牌%d、分类品牌关系%d、系列%d、型号%d、属性(父)%d、属性(子)%d、型号属性关系%d",
                    addCategory, addBrand, addCategoryBrand, addSeries, addModel, addAttrParent, addAttrChild, addModelAttr);
            log.info(summary);
            return summary;

        } catch (Exception e) {
            throw new RuntimeException("Excel导入失败:" + e.getMessage(), e);
        }
    }

    @Override
    public String importReleaseExcel(MultipartFile file) {
         try {
             ImportParams params = new ImportParams();
             params.setHeadRows(1);
             params.setTitleRows(0);
             int addRelease=0;
             int addParentRelease=0;
             List<SpuAllReleaseExcelVo> rows = ExcelImportUtil.importExcel(file.getInputStream(), SpuAllReleaseExcelVo.class, params);
             if (rows == null || rows.isEmpty()) {
                 return "导入完成：0条记录";
             }
             for (SpuAllReleaseExcelVo vo : rows) {
                 SpuReleaseInfo parent = spuReleaseInfoMapper.selectOne(new LambdaQueryWrapper<SpuReleaseInfo>()
                         .eq(SpuReleaseInfo::getId, vo.getParentId())
                         .eq(SpuReleaseInfo::getName, vo.getParentName())
                         .eq(SpuReleaseInfo::getType, 1)
                         .eq(SpuReleaseInfo::getDelFlag, 0))
                         ;
                 if (parent == null) {
                     parent = new SpuReleaseInfo();
                     parent.setType(1);
                     parent.setId(vo.getParentId());
                     parent.setName(vo.getParentName());

                     if (spuReleaseInfoMapper.insert(parent) > 0) {
                         addParentRelease++;
                     }
                 }
                 SpuReleaseInfo release = spuReleaseInfoMapper.selectOne(new LambdaQueryWrapper<SpuReleaseInfo>()
                         .eq(SpuReleaseInfo::getParentId, vo.getParentId())
                         .eq(SpuReleaseInfo::getParentName, vo.getParentName())
                         .eq(SpuReleaseInfo::getName, vo.getName())
                         .eq(SpuReleaseInfo::getType, 0)
                         .eq(SpuReleaseInfo::getDelFlag, 0));
                 if(release == null){
                     release = new SpuReleaseInfo();
                     release.setId(vo.getId());
                     release.setName(vo.getName());
                     release.setType(0);
                     release.setParentId(parent.getId());
                     release.setParentName(parent.getName());
                     release.setCategoryId(parent.getCategoryId());
                     release.setCategoryName(parent.getCategoryName());
                     release.setReleaseGroupId(parent.getReleaseGroupId());
                     release.setReleaseGroupName(parent.getReleaseGroupName());
                     if (spuReleaseInfoMapper.insert(release) > 0) {
                         addRelease++;
                     }
                 }
             }
             String summary = String.format(
                     "导入完成：新增 发布项%d,父级发布项%d", addRelease,addParentRelease);
             log.info(summary);
             return summary;
        } catch (Exception e) {
            throw new RuntimeException("Excel导入失败:" + e.getMessage(), e);
        }

    }

@Override
public String importReleaseExcelByTemplate(MultipartFile file) {
    try {
        ImportParams params = new ImportParams();
        params.setHeadRows(1);
        params.setTitleRows(0);

        int addTemplate = 0, updateTemplate = 0;
        int addGroup = 0, updateGroup = 0;
        int addParent = 0, updateParent = 0;
        int addChild = 0, updateChild = 0;
        int addTplRel = 0;

        List<SpuTemplateReleaseExcelVo> rows =
                ExcelImportUtil.importExcel(file.getInputStream(), SpuTemplateReleaseExcelVo.class, params);
        if (rows == null || rows.isEmpty()) {
            return "导入完成：0条记录";
        }

        // 收集 Excel 行中用于 upsert 的键（ID 优先，其次名称）
        Map<Long, String> templateIdNameMap = new HashMap<>();
        Map<String, Long> templateNameIdMap = new HashMap<>();
        Map<Long, String> groupIdNameMap = new HashMap<>();
        Map<String, Long> groupNameIdMap = new HashMap<>();
        Map<Long, SpuTemplateReleaseExcelVo> parentById = new HashMap<>();
        Map<String, SpuTemplateReleaseExcelVo> parentByName = new HashMap<>(); // key: groupId::parentName
        Map<Long, SpuTemplateReleaseExcelVo> childById = new HashMap<>();
        Map<String, SpuTemplateReleaseExcelVo> childByKey = new HashMap<>(); // key: parentId::releaseName

        for (SpuTemplateReleaseExcelVo vo : rows) {
            Long tId = vo.getTemplateId();
            String tName = safeTrim(vo.getTemplateName());
            if (tId != null) templateIdNameMap.put(tId, tName);
            else if (!isBlank(tName) && !templateNameIdMap.containsKey(tName)) templateNameIdMap.put(tName, null);

            Long gId = vo.getReleaseGroupId();
            String gName = safeTrim(vo.getReleaseGroupName());
            if (gId != null) groupIdNameMap.put(gId, gName);
            else if (!isBlank(gName) && !groupNameIdMap.containsKey(gName)) groupNameIdMap.put(gName, null);

            Long pId = vo.getReleaseParentId();
            String pName = safeTrim(vo.getReleaseParentName());
            if (pId != null && !parentById.containsKey(pId)) parentById.put(pId, vo);
            else if (!isBlank(pName)) parentByName.put((gId == null ? 0L : gId) + "::" + pName, vo);

            Long cId = vo.getReleaseId();
            String cName = safeTrim(vo.getReleaseName());
            if (cId != null && !childById.containsKey(cId)) childById.put(cId, vo);
            else if (!isBlank(cName) && pId != null) childByKey.put(pId + "::" + cName, vo);
        }

        // 预加载数据库中已存在的记录，避免逐行查询
        Set<Long> templateIdSet = templateIdNameMap.keySet();
        Set<Long> groupIdSet = groupIdNameMap.keySet();
        Set<Long> parentIdSet = parentById.keySet();
        Set<Long> childIdSet = childById.keySet();

        Map<Long, SpuTemplateInfo> existTemplate = new HashMap<>();
        Map<Long, SpuReleaseGroup> existGroup = new HashMap<>();
        Map<Long, SpuReleaseInfo> existParent = new HashMap<>();
        Map<Long, SpuReleaseInfo> existChild = new HashMap<>();

        if (!templateIdSet.isEmpty()) {
            spuTemplateInfoMapper.selectList(new LambdaQueryWrapper<SpuTemplateInfo>()
                    .in(true, SpuTemplateInfo::getId, templateIdSet)
                    .eq(SpuTemplateInfo::getDelFlag, 0))
                    .forEach(x -> existTemplate.put(x.getId(), x));
        }
        if (!groupIdSet.isEmpty()) {
            spuReleaseGroupMapper.selectList(new LambdaQueryWrapper<SpuReleaseGroup>()
                    .in(true, SpuReleaseGroup::getId, groupIdSet)
                    .eq(SpuReleaseGroup::getDelFlag, 0))
                    .forEach(x -> existGroup.put(x.getId(), x));
        }
        if (!parentIdSet.isEmpty()) {
            spuReleaseInfoMapper.selectList(new LambdaQueryWrapper<SpuReleaseInfo>()
                    .in(true, SpuReleaseInfo::getId, parentIdSet)
                    .eq(SpuReleaseInfo::getType, 1)
                    .eq(SpuReleaseInfo::getDelFlag, 0))
                    .forEach(x -> existParent.put(x.getId(), x));
        }
        if (!childIdSet.isEmpty()) {
            spuReleaseInfoMapper.selectList(new LambdaQueryWrapper<SpuReleaseInfo>()
                    .in(true, SpuReleaseInfo::getId, childIdSet)
                    .eq(SpuReleaseInfo::getType, 0)
                    .eq(SpuReleaseInfo::getDelFlag, 0))
                    .forEach(x -> existChild.put(x.getId(), x));
        }

        // 1) 模板 upsert：ID 优先，其次按名称
        for (Map.Entry<Long, String> e : templateIdNameMap.entrySet()) {
            Long id = e.getKey();
            String name = e.getValue();
            SpuTemplateInfo found = existTemplate.get(id);
            if (found != null) {
                boolean needUpdate = false;
                if (!isBlank(name) && (found.getTemplateName() == null || !found.getTemplateName().equals(name))) {
                    found.setTemplateName(name);
                    needUpdate = true;
                }
                if (needUpdate) {
                    spuTemplateInfoMapper.updateById(found);
                    updateTemplate++;
                }
            } else {
                SpuTemplateInfo ins = new SpuTemplateInfo();
                ins.setId(id);
                ins.setTemplateName(name);
                ins.setStatus("1");
                if (spuTemplateInfoMapper.insert(ins) > 0) {
                    addTemplate++;
                }
            }
        }
        for (String name : templateNameIdMap.keySet()) {
            SpuTemplateInfo existByName = spuTemplateInfoMapper.selectOne(new LambdaQueryWrapper<SpuTemplateInfo>()
                    .eq(SpuTemplateInfo::getTemplateName, name)
                    .eq(SpuTemplateInfo::getDelFlag, 0)
                    .last("limit 1"));
            if (existByName != null) {
                updateTemplate++;
            } else {
                SpuTemplateInfo ins = new SpuTemplateInfo();
                ins.setTemplateName(name);
                ins.setStatus("1");
                if (spuTemplateInfoMapper.insert(ins) > 0) {
                    addTemplate++;
                }
            }
        }

        // 2) 发布组 upsert：ID 优先，其次按名称
        for (Map.Entry<Long, String> e : groupIdNameMap.entrySet()) {
            Long id = e.getKey();
            String name = e.getValue();
            SpuReleaseGroup found = existGroup.get(id);
            if (found != null) {
                boolean needUpdate = false;
                if (!isBlank(name) && (found.getGroupName() == null || !found.getGroupName().equals(name))) {
                    found.setGroupName(name);
                    needUpdate = true;
                }
                if (needUpdate) {
                    spuReleaseGroupMapper.updateById(found);
                    updateGroup++;
                }
            } else {
                SpuReleaseGroup ins = new SpuReleaseGroup();
                ins.setId(id);
                ins.setGroupName(name);
                ins.setStatus("1");
                ins.setSort(0);
                if (spuReleaseGroupMapper.insert(ins) > 0) {
                    addGroup++;
                }
            }
        }
        for (String name : groupNameIdMap.keySet()) {
            SpuReleaseGroup existByName = spuReleaseGroupMapper.selectOne(new LambdaQueryWrapper<SpuReleaseGroup>()
                    .eq(SpuReleaseGroup::getGroupName, name)
                    .eq(SpuReleaseGroup::getDelFlag, 0)
                    .last("limit 1"));
            if (existByName != null) {
                updateGroup++;
            } else {
                SpuReleaseGroup ins = new SpuReleaseGroup();
                ins.setGroupName(name);
                ins.setStatus("1");
                ins.setSort(0);
                if (spuReleaseGroupMapper.insert(ins) > 0) {
                    addGroup++;
                }
            }
        }

        // 3) 父级发布项 upsert：ID 优先，其次按 group+name
        for (SpuTemplateReleaseExcelVo vo : parentById.values()) {
            Long id = vo.getReleaseParentId();
            String name = safeTrim(vo.getReleaseParentName());
            Long gId = vo.getReleaseGroupId();
            String gName = safeTrim(vo.getReleaseGroupName());
            SpuReleaseInfo found = existParent.get(id);
            if (found != null) {
                boolean needUpdate = false;
                if (!isBlank(name) && (found.getName() == null || !found.getName().equals(name))) {
                    found.setName(name);
                    needUpdate = true;
                }
                if (gId != null && (found.getReleaseGroupId() == null || !found.getReleaseGroupId().equals(gId))) {
                    found.setReleaseGroupId(gId);
                    needUpdate = true;
                }
                if (!isBlank(gName) && (found.getReleaseGroupName() == null || !found.getReleaseGroupName().equals(gName))) {
                    found.setReleaseGroupName(gName);
                    needUpdate = true;
                }
                if (needUpdate) {
                    spuReleaseInfoMapper.updateById(found);
                    updateParent++;
                }
            } else {
                SpuReleaseInfo ins = new SpuReleaseInfo();
                ins.setId(id);
                ins.setName(name);
                ins.setType(1);
                ins.setParentId(0L);
                ins.setParentName(null);
                ins.setReleaseGroupId(gId);
                ins.setReleaseGroupName(gName);
                ins.setStatus("1");
                if (spuReleaseInfoMapper.insert(ins) > 0) {
                    addParent++;
                }
            }
        }
        for (SpuTemplateReleaseExcelVo vo : parentByName.values()) {
            String name = safeTrim(vo.getReleaseParentName());
            Long gId = vo.getReleaseGroupId();
            String gName = safeTrim(vo.getReleaseGroupName());
            SpuReleaseInfo existByName = spuReleaseInfoMapper.selectOne(new LambdaQueryWrapper<SpuReleaseInfo>()
                    .eq(SpuReleaseInfo::getName, name)
                    .eq(SpuReleaseInfo::getType, 1)
                    .eq(SpuReleaseInfo::getDelFlag, 0)
                    .last("limit 1"));
            if (existByName != null) {
                boolean needUpdate = false;
                if (gId != null && (existByName.getReleaseGroupId() == null || !existByName.getReleaseGroupId().equals(gId))) {
                    existByName.setReleaseGroupId(gId);
                    needUpdate = true;
                }
                if (!isBlank(gName) && (existByName.getReleaseGroupName() == null || !existByName.getReleaseGroupName().equals(gName))) {
                    existByName.setReleaseGroupName(gName);
                    needUpdate = true;
                }
                if (needUpdate) {
                    spuReleaseInfoMapper.updateById(existByName);
                    updateParent++;
                }
            } else {
                SpuReleaseInfo ins = new SpuReleaseInfo();
                ins.setName(name);
                ins.setType(1);
                ins.setParentId(0L);
                ins.setReleaseGroupId(gId);
                ins.setReleaseGroupName(gName);
                ins.setStatus("1");
                if (spuReleaseInfoMapper.insert(ins) > 0) {
                    addParent++;
                }
            }
        }

        // 4) 子级发布项 upsert：ID 优先，其次按 parentId+name
        for (SpuTemplateReleaseExcelVo vo : childById.values()) {
            Long id = vo.getReleaseId();
            String name = safeTrim(vo.getReleaseName());
            Long pId = vo.getReleaseParentId();
            String pName = safeTrim(vo.getReleaseParentName());
            Long gId = vo.getReleaseGroupId();
            String gName = safeTrim(vo.getReleaseGroupName());
            SpuReleaseInfo found = existChild.get(id);
            if (found != null) {
                boolean needUpdate = false;
                if (!isBlank(name) && (found.getName() == null || !found.getName().equals(name))) {
                    found.setName(name);
                    needUpdate = true;
                }
                if (pId != null && (found.getParentId() == null || !found.getParentId().equals(pId))) {
                    found.setParentId(pId);
                    needUpdate = true;
                }
                if (!isBlank(pName) && (found.getParentName() == null || !found.getParentName().equals(pName))) {
                    found.setParentName(pName);
                    needUpdate = true;
                }
                if (gId != null && (found.getReleaseGroupId() == null || !found.getReleaseGroupId().equals(gId))) {
                    found.setReleaseGroupId(gId);
                    needUpdate = true;
                }
                if (!isBlank(gName) && (found.getReleaseGroupName() == null || !found.getReleaseGroupName().equals(gName))) {
                    found.setReleaseGroupName(gName);
                    needUpdate = true;
                }
                if (needUpdate) {
                    spuReleaseInfoMapper.updateById(found);
                    updateChild++;
                }
            } else {
                SpuReleaseInfo ins = new SpuReleaseInfo();
                ins.setId(id);
                ins.setName(name);
                ins.setType(0);
                ins.setParentId(pId);
                ins.setParentName(pName);
                ins.setReleaseGroupId(gId);
                ins.setReleaseGroupName(gName);
                ins.setStatus("1");
                if (spuReleaseInfoMapper.insert(ins) > 0) {
                    addChild++;
                }
            }
        }
        for (SpuTemplateReleaseExcelVo vo : childByKey.values()) {
            String name = safeTrim(vo.getReleaseName());
            Long pId = vo.getReleaseParentId();
            String pName = safeTrim(vo.getReleaseParentName());
            Long gId = vo.getReleaseGroupId();
            String gName = safeTrim(vo.getReleaseGroupName());
            SpuReleaseInfo existByName = spuReleaseInfoMapper.selectOne(new LambdaQueryWrapper<SpuReleaseInfo>()
                    .eq(SpuReleaseInfo::getParentId, pId)
                    .eq(SpuReleaseInfo::getName, name)
                    .eq(SpuReleaseInfo::getType, 0)
                    .eq(SpuReleaseInfo::getDelFlag, 0)
                    .last("limit 1"));
            if (existByName != null) {
                boolean needUpdate = false;
                if (!isBlank(pName) && (existByName.getParentName() == null || !existByName.getParentName().equals(pName))) {
                    existByName.setParentName(pName);
                    needUpdate = true;
                }
                if (gId != null && (existByName.getReleaseGroupId() == null || !existByName.getReleaseGroupId().equals(gId))) {
                    existByName.setReleaseGroupId(gId);
                    needUpdate = true;
                }
                if (!isBlank(gName) && (existByName.getReleaseGroupName() == null || !existByName.getReleaseGroupName().equals(gName))) {
                    existByName.setReleaseGroupName(gName);
                    needUpdate = true;
                }
                if (needUpdate) {
                    spuReleaseInfoMapper.updateById(existByName);
                    updateChild++;
                }
            } else {
                SpuReleaseInfo ins = new SpuReleaseInfo();
                ins.setName(name);
                ins.setType(0);
                ins.setParentId(pId);
                ins.setParentName(pName);
                ins.setReleaseGroupId(gId);
                ins.setReleaseGroupName(gName);
                ins.setStatus("1");
                if (spuReleaseInfoMapper.insert(ins) > 0) {
                    addChild++;
                }
            }
        }

        // 5) 模板-发布项关联 插入（ID 优先，自动补齐缺失 ID；不做更新）
        Map<Long, Long> templateActiveVersionMap = new HashMap<>();
        Map<String, Long> templateNameCache = new HashMap<>();
        Map<String, Long> groupNameCache = new HashMap<>();
        Map<String, Long> parentKeyCache = new HashMap<>(); // key: groupId::parentName
        Map<String, Long> childKeyCache = new HashMap<>();  // key: parentId::releaseName

        for (SpuTemplateReleaseExcelVo vo : rows) {
            Long templateId = vo.getTemplateId();
            String templateName = safeTrim(vo.getTemplateName());
            if (templateId == null && !isBlank(templateName)) {
                templateId = templateNameCache.computeIfAbsent(templateName, nm -> {
                    SpuTemplateInfo t = spuTemplateInfoMapper.selectOne(new LambdaQueryWrapper<SpuTemplateInfo>()
                            .eq(SpuTemplateInfo::getTemplateName, nm)
                            .eq(SpuTemplateInfo::getDelFlag, 0)
                            .last("limit 1"));
                    return t != null ? t.getId() : null;
                });
            }
            if (templateId == null) {
                // 无法关联模板，跳过该行
                continue;
            }

            // 解析并/或创建模板的活动版本
            Long versionId = templateActiveVersionMap.get(templateId);
            if (versionId == null) {
                SpuTemplateVersion last = spuTemplateVersionMapper.selectOne(new LambdaQueryWrapper<SpuTemplateVersion>()
                        .eq(SpuTemplateVersion::getTemplateId, templateId)
                        .eq(SpuTemplateVersion::getDelFlag, 0)
                        .orderByDesc(SpuTemplateVersion::getVersionNo)
                        .last("limit 1"));
                int nextNo = (last == null || last.getVersionNo() == null) ? 1 : last.getVersionNo() + 1;
                SpuTemplateVersion newV = new SpuTemplateVersion();
                newV.setTemplateId(templateId);
                newV.setVersionNo(nextNo);
                spuTemplateVersionMapper.insert(newV);
                versionId = newV.getId();
                templateActiveVersionMap.put(templateId, versionId);
                SpuTemplateInfo t = spuTemplateInfoMapper.selectById(templateId);
                if (t != null) {
                    t.setActiveVersionId(versionId);
                    spuTemplateInfoMapper.updateById(t);
                }
            }

            Long groupId = vo.getReleaseGroupId();
            String groupName = safeTrim(vo.getReleaseGroupName());
            if (groupId == null && !isBlank(groupName)) {
                groupId = groupNameCache.computeIfAbsent(groupName, nm -> {
                    SpuReleaseGroup g = spuReleaseGroupMapper.selectOne(new LambdaQueryWrapper<SpuReleaseGroup>()
                            .eq(SpuReleaseGroup::getGroupName, nm)
                            .eq(SpuReleaseGroup::getDelFlag, 0)
                            .last("limit 1"));
                    return g != null ? g.getId() : null;
                });
            }

            Long parentId = vo.getReleaseParentId();
            String parentName = safeTrim(vo.getReleaseParentName());
            if (parentId == null && !isBlank(parentName) && groupId != null) {
                String key = groupId + "::" + parentName;
                Long finalGroupId = groupId;
                parentId = parentKeyCache.computeIfAbsent(key, k -> {
                    SpuReleaseInfo p = spuReleaseInfoMapper.selectOne(new LambdaQueryWrapper<SpuReleaseInfo>()
                            .eq(SpuReleaseInfo::getReleaseGroupId, finalGroupId)
                            .eq(SpuReleaseInfo::getName, parentName)
                            .eq(SpuReleaseInfo::getType, 1)
                            .eq(SpuReleaseInfo::getDelFlag, 0)
                            .last("limit 1"));
                    return p != null ? p.getId() : null;
                });
            }

            Long releaseId = vo.getReleaseId();
            String releaseName = safeTrim(vo.getReleaseName());
            if (releaseId == null && !isBlank(releaseName) && parentId != null) {
                String key = parentId + "::" + releaseName;
                Long finalParentId = parentId;
                releaseId = childKeyCache.computeIfAbsent(key, k -> {
                    SpuReleaseInfo c = spuReleaseInfoMapper.selectOne(new LambdaQueryWrapper<SpuReleaseInfo>()
                            .eq(SpuReleaseInfo::getParentId, finalParentId)
                            .eq(SpuReleaseInfo::getName, releaseName)
                            .eq(SpuReleaseInfo::getType, 0)
                            .eq(SpuReleaseInfo::getDelFlag, 0)
                            .last("limit 1"));
                    return c != null ? c.getId() : null;
                });
            }

            if (groupId == null || parentId == null) {
                // 关键 ID 缺失，无法建立关联
                continue;
            }

            SpuTemplateRelease insRel = new SpuTemplateRelease();
            insRel.setTemplateId(templateId);
            insRel.setTemplateVersionId(versionId);
            insRel.setTemplateName(templateName);
            insRel.setReleaseGroupId(groupId);
            insRel.setReleaseGroupName(groupName);
            insRel.setReleaseParentId(parentId);
            insRel.setReleaseParentName(parentName);
            insRel.setReleaseId(releaseId);
            insRel.setReleaseName(releaseName);
            if (spuTemplateReleaseMapper.insert(insRel) > 0) {
                addTplRel++;
            }
        }

        String summary = String.format(
                "导入完成：模板 新增%d 更新%d；发布组 新增%d 更新%d；父级发布项 新增%d 更新%d；发布项值 新增%d 更新%d；模板-发布关联 新增%d",
                addTemplate, updateTemplate, addGroup, updateGroup, addParent, updateParent, addChild, updateChild, addTplRel);
        log.info(summary);
        return summary;
    } catch (Exception e) {
        throw new RuntimeException("Excel导入失败:" + e.getMessage(), e);
    }
}

    @Override
    public String importModelTemplate(MultipartFile file) {
        try {

            ImportParams params = new ImportParams();
            params.setHeadRows(1);
            params.setTitleRows(0);
            int addRelease=0;
            List<SpuModelTemplateExcelVo> rows = ExcelImportUtil.importExcel(file.getInputStream(), SpuModelTemplateExcelVo.class, params);
            log.info("导入模板：{}", rows.size());
            for(SpuModelTemplateExcelVo vo:rows) {
              SpuModelTemplate modelTemplate = spuModelTemplateMapper.selectOne(new LambdaQueryWrapper<SpuModelTemplate>()
                      .eq(SpuModelTemplate::getModelId, vo.getId())
                      .eq(SpuModelTemplate::getTemplateId, vo.getTemplateId())
                      .eq(SpuModelTemplate::getDelFlag, 0)
                      .last("limit 1"));
              SpuTemplateVersion last = spuTemplateVersionMapper.selectOne(
                      new LambdaQueryWrapper<SpuTemplateVersion>()
                              .eq(SpuTemplateVersion::getTemplateId, vo.getTemplateId())
                              .eq(SpuTemplateVersion::getDelFlag, 0)
                              .orderByDesc(SpuTemplateVersion::getVersionNo).last("limit 1"));
              if(modelTemplate==null) {
                  modelTemplate = new SpuModelTemplate();
                  modelTemplate.setModelId(vo.getId());
                  modelTemplate.setTemplateId(vo.getTemplateId());
                  modelTemplate.setModelName(vo.getModelName());
                  modelTemplate.setTemplateName(vo.getTemplateName());
                  modelTemplate.setActiveVersionId(last.getId());
                  if(spuModelTemplateMapper.insert(modelTemplate)>0){
                      addRelease++;
                  }
              }else {
                  modelTemplate.setModelId(vo.getId());
                  modelTemplate.setTemplateId(vo.getTemplateId());
                  modelTemplate.setModelName(vo.getModelName());
                  modelTemplate.setTemplateName(vo.getTemplateName());
                  modelTemplate.setActiveVersionId(last.getId());
                  if(spuModelTemplateMapper.updateById(modelTemplate)>0){
                      addRelease++;
                  }
              }

          }
            return String.format("导入完成：型号-模板 新增%d",addRelease);
        } catch (Exception e) {
            throw new RuntimeException("Excel导入失败:" + e.getMessage(), e);
        }

    }


    private static String safeTrim(String s) {
        return s == null ? null : s.trim();
    }

    private static boolean isBlank(String s) {
        return s == null || s.trim().isEmpty();
    }
}